package com.passion.utils;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSONObject;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import static com.passion.common.Constants.*;

/**
 * @program offline-e-commerce-data-warehouse
 * @Description 数据库连接池工具类
 * @Author limeihua
 * @Date 2024/09/29 20:42
 * @Version 1.0
 */
public class DatabaseUtil {
    private static final DruidDataSource dataSource;
    private static ThreadLocal<Connection> connectionThreadLocal = new ThreadLocal<>();

    static {
        dataSource = new DruidDataSource();
        dataSource.setUrl(HIVE_JDBC_URL);
        dataSource.setDriverClassName(HIVE_JDBC_DRIVER);
        dataSource.setUsername(HIVE_USERNAME);
        dataSource.setInitialSize(5);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setMinIdle(5);
        dataSource.setMaxActive(20);
        dataSource.setSocketTimeout(1000000000);
        dataSource.setQueryTimeout(1000000000);
    }

    public static DruidDataSource getDataSource() {
        if (dataSource != null) {
            return dataSource;
        } else {
            throw new RuntimeException("数据源获取失败");
        }
    }

    public static Connection getHiveConnection() {
        try {
            Connection conn = connectionThreadLocal.get();
            if (conn == null) {
                conn = dataSource.getConnection();
                connectionThreadLocal.set(conn);
            }
            return conn;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public static void closeHiveConnection() {
        Connection conn = connectionThreadLocal.get();
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            } finally {
                connectionThreadLocal.remove();
            }
        }
    }

    public static void main(String[] args) {
        DruidDataSource dataSource = getDataSource();
        try {
            DruidPooledConnection connection = dataSource.getConnection();
            ResultSet resultSet = connection.prepareStatement("with\n" +
                    "sku as\n" +
                    "(\n" +
                    "    select\n" +
                    "        id,\n" +
                    "        price,\n" +
                    "        sku_name,\n" +
                    "        sku_desc,\n" +
                    "        weight,\n" +
                    "        is_sale,\n" +
                    "        spu_id,\n" +
                    "        category3_id,\n" +
                    "        tm_id,\n" +
                    "        create_time\n" +
                    "    from ods_sku_info_full\n" +
                    "    where dt='2022-06-08'\n" +
                    "),\n" +
                    "spu as\n" +
                    "(\n" +
                    "    select\n" +
                    "        id,\n" +
                    "        spu_name\n" +
                    "    from ods_spu_info_full\n" +
                    "    where dt='2022-06-08'\n" +
                    "),\n" +
                    "c3 as\n" +
                    "(\n" +
                    "    select\n" +
                    "        id,\n" +
                    "        name,\n" +
                    "        category2_id\n" +
                    "    from ods_base_category3_full\n" +
                    "    where dt='2022-06-08'\n" +
                    "),\n" +
                    "c2 as\n" +
                    "(\n" +
                    "    select\n" +
                    "        id,\n" +
                    "        name,\n" +
                    "        category1_id\n" +
                    "    from ods_base_category2_full\n" +
                    "    where dt='2022-06-08'\n" +
                    "),\n" +
                    "c1 as\n" +
                    "(\n" +
                    "    select\n" +
                    "        id,\n" +
                    "        name\n" +
                    "    from ods_base_category1_full\n" +
                    "    where dt='2022-06-08'\n" +
                    "),\n" +
                    "tm as\n" +
                    "(\n" +
                    "    select\n" +
                    "        id,\n" +
                    "        tm_name\n" +
                    "    from ods_base_trademark_full\n" +
                    "    where dt='2022-06-08'\n" +
                    "),\n" +
                    "attr as\n" +
                    "(\n" +
                    "    select\n" +
                    "        sku_id,\n" +
                    "        collect_set(named_struct('attr_id',attr_id,'value_id',value_id,'attr_name',attr_name,'value_name',value_name)) attrs\n" +
                    "    from ods_sku_attr_value_full\n" +
                    "    where dt='2022-06-08'\n" +
                    "    group by sku_id\n" +
                    "),\n" +
                    "sale_attr as\n" +
                    "(\n" +
                    "    select\n" +
                    "        sku_id,\n" +
                    "        collect_set(named_struct('sale_attr_id',sale_attr_id,'sale_attr_value_id',sale_attr_value_id,'sale_attr_name',sale_attr_name,'sale_attr_value_name',sale_attr_value_name)) sale_attrs\n" +
                    "    from ods_sku_sale_attr_value_full\n" +
                    "    where dt='2022-06-08'\n" +
                    "    group by sku_id\n" +
                    ")\n" +
                    "select\n" +
                    "    sku.id,\n" +
                    "    sku.price,\n" +
                    "    sku.sku_name,\n" +
                    "    sku.sku_desc,\n" +
                    "    sku.weight,\n" +
                    "    sku.is_sale,\n" +
                    "    sku.spu_id,\n" +
                    "    spu.spu_name,\n" +
                    "    sku.category3_id,\n" +
                    "    c3.name,\n" +
                    "    c3.category2_id,\n" +
                    "    c2.name,\n" +
                    "    c2.category1_id,\n" +
                    "    c1.name,\n" +
                    "    sku.tm_id,\n" +
                    "    tm.tm_name,\n" +
                    "    attr.attrs,\n" +
                    "    sale_attr.sale_attrs,\n" +
                    "    sku.create_time\n" +
                    "from sku\n" +
                    "left join spu on sku.spu_id=spu.id\n" +
                    "left join c3 on sku.category3_id=c3.id\n" +
                    "left join c2 on c3.category2_id=c2.id\n" +
                    "left join c1 on c2.category1_id=c1.id\n" +
                    "left join tm on sku.tm_id=tm.id\n" +
                    "left join attr on sku.id=attr.sku_id\n" +
                    "left join sale_attr on sku.id=sale_attr.sku_id\n").executeQuery();
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();
            while (resultSet.next()) {
                JSONObject jsonObject = new JSONObject();
                for (int i = 1; i <= columnCount; i++) {
                    Object object = resultSet.getObject(i);
                    jsonObject.put(metaData.getColumnName(i), object);
                }
                System.out.println(jsonObject);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}
